<?php

Class quiz_model extends CI_Model {

    function update($quizInfo) {
        $this->db->where('quid', $quizInfo['quid']);
        $this->db->update('quiz', $quizInfo);
        return "Đã cập nhật!";
    }

    function get_number_of_attempt($quid, $user_id) {
        $this->db->select('*');
        $this->db->from('quiz_result');
        $this->db->where('uid', $user_id);
        $this->db->where('quid', $quid);
        $query = $this->db->get();

        return $query->num_rows();
    }

    function get($id) {

        $this->db->select('*');
        $this->db->from('quiz');
        $this->db->where('quid', $id);

        $query = $this->db->get();
        return $query->row_array();
    }

    function get_last() {

        $this->db->select('*');

        $this->db->from('quiz');

        $this->db->order_by("quid", "desc");
        $this->db->limit('1');
        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row_array();
        } else {
            return false;
        }
    }

    function get_last_of_group($gid) {

        $this->db->select('*');
        $this->db->from('quiz_group');
        $this->db->where('gid', $gid);
        $this->db->order_by("qgid", "desc");
        $query = $this->db->get();
        $row = $query->row_array();

        $this->db->select('*');
        $this->db->from('quiz');
        //$this->db->join('quiz_group', 'quiz_group.quid = quiz.quid');
        $this->db->where('quid', $row['quid']);
        //$this->db->order_by("quiz_group.qgid", "desc");
        $this->db->limit('1');

        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row_array();
        } else {
            return false;
        }
    }

    function get_last_of_group_and_evaluation($gid, $eid) {

        $this->db->select('*');
        $this->db->from('quiz_group');
        $this->db->where('gid', $gid);
        $this->db->order_by("qgid", "desc");
        $query = $this->db->get();
        $row = $query->row_array();

        $this->db->select('*');
        $this->db->from('quiz');
        //$this->db->join('quiz_group', 'quiz_group.quid = quiz.quid');
        $this->db->where('quid', $row['quid']);
        $this->db->where('eid', $eid);
        //$this->db->order_by("quiz_group.qgid", "desc");
        $this->db->limit('1');

        $query = $this->db->get();
        if ($query->num_rows() > 0) {
            return $query->row_array();
        } else {
            return false;
        }
    }

    function quiz_list($limit, $gid = '') {
        $institute_id = $this->session->userdata('institute_id');
        $nor = $this->config->item('number_of_rows');
        if ($this->input->post('search_type')) {
            $search_type = $this->input->post('search_type');
            $search = $this->input->post('search');


            if ($gid != "") {
                $query = $this->db->query("select quiz.* from quiz join quiz_group on quiz.quid=quiz_group.quid where quiz_group.gid='$gid' and $search_type like '%$search%' and quiz.institute_id = '$institute_id' order by quid desc limit $limit, $nor");
            } else {
                $query = $this->db->query("select quiz.* from quiz where $search_type like '%$search%' and quiz.institute_id = '$institute_id' order by quid desc limit $limit, $nor");
            }
        } else {
            if ($gid != "") {
                $query = $this->db->query("select quiz.* from quiz join quiz_group on quiz.quid=quiz_group.quid where quiz_group.gid='$gid' and quiz.institute_id = '$institute_id' order by quid desc limit $limit, $nor");
            } else {
                $query = $this->db->query("select quiz.* from quiz where quiz.institute_id = '$institute_id' order by quid desc limit $limit, $nor");
            }
        }
        if ($query->num_rows() >= 1) {
            return $query->result();
        } else {
            return false;
        }
    }
    
    function getNumberOfRights($questionId, $evaluationId)
    {
//        $queryString = "select count(*) as 'Rights' from `quiz_result` where `qids` like '%$questionId%' and `oids` like concat('%',(select `oid` from `q_options` where `qid` = $questionId and `score` = 1),'%') and `quid` in (SELECT `quid` FROM `quiz` WHERE `eid` = $evaluationId)";
//        $query = $this->db->query($queryString);
//        $result = $query->row_array();
//        $rights = $result['Rights'];
        
        $queryString = "select count(*) as 'Rights' from `quiz_result` where `oids` like concat((select `oid` from `q_options` where `qid` = $questionId and `score` = 1),',%') and `quid` in (SELECT `quid` FROM `quiz` WHERE `eid` = $evaluationId)";
        $query = $this->db->query($queryString);
        $result = $query->row_array();
        $rights = $result['Rights'];
        
        $queryString2 = "select count(*) as 'Rights' from `quiz_result` where `oids` like concat('%,',(select `oid` from `q_options` where `qid` = $questionId and `score` = 1),',%') and `quid` in (SELECT `quid` FROM `quiz` WHERE `eid` = $evaluationId)";
        $query2 = $this->db->query($queryString2);
        $result2 = $query2->row_array();
        $rights2 = $result2['Rights'];
        
        $queryString3 = "select count(*) as 'Rights' from `quiz_result` where `oids` like concat('%,',(select `oid` from `q_options` where `qid` = $questionId and `score` = 1)) and `quid` in (SELECT `quid` FROM `quiz` WHERE `eid` = $evaluationId)";
        $query3 = $this->db->query($queryString3);
        $result3 = $query3->row_array();
        $rights3 = $result3['Rights'];
        
        return $rights + $rights2 + $rights3;
    }
    
    function getNumberOfWrongs($questionId, $evaluationId)
    {
        // lấy những bài không có chứa câu trả lời đúng (khác nhau chữ not like)
//        $queryString = "select count(*) as 'Total' from `quiz_result` where `qids` like '%$questionId%' and `oids` not like concat('%',(select `oid` from `q_options` where `qid` = $questionId and `score` = 1),'%') and `quid` in (SELECT `quid` FROM `quiz` WHERE `eid` = $evaluationId)";
//        $query = $this->db->query($queryString);
//        $result = $query->row_array();
//        $total = $result['Total'];
//        return $total;
        
        // tổng số những bài có câu hỏi đó - số ng` đúng
        $queryString = "select count(*) as 'Total' from `quiz_result` where `qids` like '%$questionId%' and `quid` in (SELECT `quid` FROM `quiz` WHERE `eid` = $evaluationId)";
        $query = $this->db->query($queryString);
        $result = $query->row_array();
        $total = $result['Total'];
        
        return $total - $this->getNumberOfRights($questionId, $evaluationId);
    }

    // Tam
    function insert($newRecord) {
        if ($this->db->insert('quiz', $newRecord)) {
            $quid = $this->db->insert_id();

            foreach ($newRecord->assigned_groups as $value) {
                $insert_data = array(
                    'quid' => $quid,
                    'gid' => $value,
                    'institute_id' => 1
                );
                $this->db->insert('quiz_group', $insert_data);
            }

            $questionsSets = $newRecord->no_of_questions;
            foreach ($questionsSets as $eachQuestionsSet) {
                $insert_data = array(
                    'quid' => $quid,
                    'cid' => $eachQuestionsSet['cid'],
                    'did' => $eachQuestionsSet['did'],
                    'no_of_questions' => $eachQuestionsSet['no_of_questions'],
                    'institute_id' => $eachQuestionsSet['institute_id']
                );

                $this->db->insert('quiz_qids', $insert_data);
            }

            return "Đã thêm!";
        } else {
            return "Unable to add";
        }
    }

    // add new quiz
    function add_quiz() {
        $institute_id = $this->session->userdata('institute_id');

        $insert_data = array(
            'quiz_name' => $this->input->post('quiz_name'),
            'description' => $this->input->post('description'),
            'start_time' => strtotime($this->input->post('test_start_time')),
            'end_time' => strtotime($this->input->post('test_end_time')),
            'duration' => $this->input->post('quiz_time_duration'),
            //'pass_percentage' => $this->input->post('pass_percentage'),
            //'test_type' => $this->input->post('test_type'),
            //'credit' => $this->input->post('test_charges'),
            //'view_answer' => $this->input->post('view_answer'),
            //'max_attempts' => $this->input->post('max_attemp'),
//            'correct_score' => $this->input->post('correct_answer_score'),
//            'incorrect_score' => $this->input->post('incorrect_answer_score'),
            'institute_id' => $institute_id,
            'eid' => $this->input->post('eid')
        );


        if ($this->db->insert('quiz', $insert_data)) {
            $quid = $this->db->insert_id();
            foreach ($_POST['assigned_groups'] as $value) {
                $insert_data = array(
                    'quid' => $quid,
                    'gid' => $value,
                    'institute_id' => $institute_id
                );


                $this->db->insert('quiz_group', $insert_data);
            }
            $noq = $this->input->post('no_of_questions');
            $insert_data = array(
                'quid' => $quid,
                'cid' => $this->input->post('cid'),
                'did' => $this->input->post('did'),
                'no_of_questions' => $noq['0'],
                'institute_id' => $institute_id
            );


            $this->db->insert('quiz_qids', $insert_data);
            return "Đã lưu bài trắc nghiệm mới";
        } else {
            return "Unable to add quiz";
        }
    }

    // update individual question time spent
    function update_time($id, $qtime) {
        $institute_id = $this->session->userdata('institute_id');
        $rid = $this->input->cookie('rid', TRUE);
        $query = $this->db->query("select quiz_result.* from quiz_result where rid='$rid' and institute_id = '$institute_id'");
        $row = $query->row_array();
        $time_spent_ind = explode(",", $row['time_spent_ind']);
        foreach ($time_spent_ind as $key => $val) {
            if ($key == $id) {
                $time_spent_ind[$key]+=$qtime;
            }
        }
        $time_spent_ind = implode(",", $time_spent_ind);
        $this->db->query("update quiz_result set time_spent_ind='$time_spent_ind' where institute_id = '$institute_id' and rid='$rid' ");
    }

// update answer
    function update_answer($id, $oid) {
        $institute_id = $this->session->userdata('institute_id');

        $rid = $this->input->cookie('rid', TRUE);
        $query = $this->db->query("select quiz_result.* from quiz_result where rid='$rid' and institute_id = '$institute_id'");
        $row = $query->row_array();
        $oids = explode(",", $row['oids']);
        foreach ($oids as $key => $val) {
            if ($key == $id) {
                $oids[$key] = $oid;
            }
        }
        $oids = implode(",", $oids);
        $this->db->query("update quiz_result set oids='$oids' where rid='$rid' and institute_id = '$institute_id'");
    }

    // add new quiz
    function edit_quiz($id) {
        $institute_id = $this->session->userdata('institute_id');
        //echo "<pre>";
        //print_r($_POST);
        //echo "</pre>";
        $insert_data = array(
            'quiz_name' => $this->input->post('quiz_name'),
            'description' => $this->input->post('description'),
            'start_time' => strtotime($this->input->post('test_start_time')),
            'end_time' => strtotime($this->input->post('test_end_time')),
            'duration' => $this->input->post('quiz_time_duration'),
//            'pass_percentage' => $this->input->post('pass_percentage'),
//            'test_type' => $this->input->post('test_type'),
//            'credit' => $this->input->post('test_charges'),
//            'view_answer' => $this->input->post('view_answer'),
//            'max_attempts' => $this->input->post('max_attemp'),
//            'correct_score' => $this->input->post('correct_answer_score'),
//            'incorrect_score' => $this->input->post('incorrect_answer_score'),
            'institute_id' => $institute_id,
            'eid' => $this->input->post('eid')
        );
        $quid = $id;
        $this->db->where('institute_id', $institute_id);
        $this->db->where('quid', $quid);
        if ($this->db->update('quiz', $insert_data)) {

            // delete existing gids and re insert new one

            $this->db->where('institute_id', $institute_id);
            $this->db->where('quid', $quid);
            $this->db->delete('quiz_group');
            foreach ($_POST['assigned_groups'] as $value) {
                $insert_data = array(
                    'quid' => $quid,
                    'gid' => $value,
                    'institute_id' => $institute_id
                );
                $this->db->insert('quiz_group', $insert_data);
            }
            // delete existing no of question record and insert new one.

            $this->db->where('institute_id', $institute_id);
            $this->db->where('quid', $quid);
            $this->db->delete('quiz_qids');
            foreach ($_POST['cid'] as $qkey => $value) {
                if ($_POST['no_of_questions'][$qkey] >= "1") {
                    $insert_data = array(
                        'quid' => $quid,
                        'cid' => $_POST['cid'][$qkey],
                        'did' => $_POST['did'][$qkey],
                        'no_of_questions' => $_POST['no_of_questions'][$qkey],
                        'institute_id' => $institute_id
                    );

                    $this->db->insert('quiz_qids', $insert_data);
                }
            }
            return "Quiz added successfully";
        } else {
            return "Unable to add quiz";
        }
    }

    function assigned_groups($id) {
        $institute_id = $this->session->userdata('institute_id');

        $query = $this->db->query("select quiz_group.* from quiz_group where quid='$id' and institute_id = '$institute_id' ");

        if ($query->num_rows() >= 1) {
            $resultdata = $query->result();
            $gids = array();
            foreach ($resultdata as $value) {
                $gids[] = $value->gid;
            }
            return $gids;
        } else {
            return false;
        }
    }

    function assigned_questions($id) {
        $institute_id = $this->session->userdata('institute_id');

        $query = $this->db->query("select quiz_qids.* from quiz_qids where quid='$id' and institute_id = '$institute_id' ");
        return $query->result_array();
    }

    function remove_quiz($id) {
        $institute_id = $this->session->userdata('institute_id');
        $this->db->where('institute_id', $institute_id);
        if ($this->db->delete('quiz', array('quid' => $id))) {

            $this->db->where('institute_id', $institute_id);
            $this->db->delete('quiz_qids', array('quid' => $id));

            $this->db->where('institute_id', $institute_id);
            $this->db->delete('quiz_group', array('quid' => $id));
            return true;
        } else {
            return false;
        }
    }

    function get_quiz_data($id) {
        $institute_id = $this->session->userdata('institute_id');
        $query = $this->db->query("select quiz.* from quiz where quid='$id' and institute_id = '$institute_id'");
        return $query->row_array();
    }

    function get_question($rid) {
        $institute_id = $this->session->userdata('institute_id');

        $query = $this->db->query("select quiz_result.* from quiz_result where rid='$rid' and institute_id='$institute_id'");
        $row = $query->row_array();
        $qids = $row['qids'];

        $query = $this->db->query("SELECT * FROM  `qbank` JOIN question_category ON qbank.cid = question_category.cid WHERE qbank.qid IN ( $qids ) and qbank.institute_id ='$institute_id'");
        $questions = $query->result_array();
        $query = $this->db->query("SELECT * FROM  q_options WHERE qid IN ( $qids )  and institute_id = '$institute_id'");
        $options = $query->result_array();
        $dataarr = array($questions, $options);
        return $dataarr;
    }

    function get_time_info($rid) {
        $institute_id = $this->session->userdata('institute_id');

        $current_time = time();
        $this->db->query("update quiz_result set time_spent=($current_time-start_time) where rid='$rid' and institute_id = '$institute_id' ");

        $query = $this->db->query("select quiz_result.* from quiz_result where rid='$rid' and institute_id ='$institute_id'");
        return $query->row_array();
    }

    function quiz_detail($id) {
        $institute_id = $this->session->userdata('institute_id');
        $query = $this->db->query("select quiz.* from quiz where quid='$id' and institute_id = '$institute_id' ");

        if ($query->num_rows() >= 1) {
            return $query->row();
        } else {
            return false;
        }
    }

    function quiz_verify($id) {
        $institute_id = $this->session->userdata('institute_id');
        $userdata = $this->session->userdata('logged_in');

        $isManager = false;
        if ($userdata['title'] == 'HR' || $userdata['title'] == 'SD') {
            $isManager = true;
        }


        if ($this->input->cookie('rid', TRUE)) {
            //check if there is any test already started
            $rid = $this->input->cookie('rid', TRUE);
            $query = $this->db->query("select quiz_result.* from quiz_result where rid='$rid' and institute_id ='$institute_id'");
            $quiz_result = $query->row_array();
            $time_spent = $quiz_result['time_spent'];
            $quid = $quiz_result['quid'];

            $query = $this->db->query("select quiz.* from quiz where quid='$quid' and institute_id ='$institute_id'");
            $quiz = $query->row_array();
            $start_time = $quiz['start_time'];
            $end_time = $quiz['end_time'];
            $duration = $quiz['duration'];
            // check quiz end time
            if ($end_time <= time() && $isManager == false) {
                return "Đã hết thời hạn làm bài."; // "Quiz not available. Available time has been passed  ";
            }
            if ($time_spent >= ($duration * 60 ) && $isManager == false) {
                return "Hết giờ làm bài!"; // "Time over";
            }
            return "1";
        } else {
            // check for new test attempt
            $query = $this->db->query("select quiz.* from quiz where quid='$id' and institute_id = '$institute_id'");
            $quiz = $query->row_array();
            $quid = $quiz['quid'];
            //$userdata = $this->session->userdata('logged_in');
            $uid = $userdata['id'];
            $gid = $userdata['gid'];

            $query = $this->db->query("select users.* from users where id='$uid' and institute_id = '$institute_id'");
            $user = $query->row_array();
            
            $userCode = $user['code'];
            $queryDoAgain = $this->db->query("select * from do_again where QuizId = '$id' and UserCode = '$userCode' order by Id desc");
            $doAgainRecord = $queryDoAgain->row_array();
            
            // kiểm tra đang nghỉ thai sản
            if ($user['status'] == 'pregnant') {
                return 'Bạn đang trong kì nghỉ thai sản, không cần phải làm bài.';
            }

            // if quiz assign to user group
            $query = $this->db->query("select quiz_group.* from quiz_group where gid='$gid' and quid='$quid' and institute_id = '$institute_id' ");
            $assigngid = $query->num_rows();
            if ($assigngid != "1" && $isManager == false) {
                return "Bài test này không được giao cho bạn làm."; // "This quiz is not assigned to your group";
            }
            
            // check quiz start time
            if ($quiz['start_time'] >= time() && $isManager == false) {
                return "Chưa đến thời hạn làm bài."; // "Quiz is not available yet!";
            }
            
            // check quiz end time
            if ($quiz['end_time'] <= time() && $isManager == false) {
                return "Đã hết thời hạn làm bài."; // "Quiz not available. Available time has been passed  ";
            }
            
            // check maximum attempts
            $query = $this->db->query("select quiz_result.* from quiz_result where uid='$uid' and quid='$quid' and institute_id = '$institute_id'");
            $attempted = $query->num_rows();
            //if ($attempted >= $quiz['max_attempts'] && $isManager == false) { // trước khi dùng cách làm lại bài test mới
            if ($attempted >= $quiz['max_attempts'] && $isManager == false && ($doAgainRecord == false || ($doAgainRecord != false && $doAgainRecord['Status'] == 'done'))) { // sau khi thực hiện cách làm lại bài test mới
                return "Bạn đã làm bài kiểm tra này rồi. Nếu bạn có thắc mắc về bài kiểm tra, xin vui lòng liên hệ với quản lý trực tiếp / giám sát bán hàng."; // "You have been reached maximum attempts available for this quiz";
            }
            
            // check if user have sufficient credit
            if ($quiz['test_type'] == 1 && $user['credit'] < $quiz['credit'] && $isManager == false) {
                return "Bạn đã làm bài kiểm tra này rồi. Nếu bạn có thắc mắc về bài kiểm tra, xin vui lòng liên hệ với quản lý trực tiếp / giám sát bán hàng."; // "You don't have sufficient credit to attempt this quiz";
            }
            
            // fetch assigned quids
            $qids = array();
            $category_names = array();
            $qids_range = array();
            $query = $this->db->query("select quiz_qids.* from quiz_qids where quid='$quid' and institute_id = '$institute_id' ");
            $result = $query->result_array();
            $rng = 0;
            foreach ($result as $key => $val) {
                $cid = $val['cid'];
                $did = $val['did'];
                $noq = $val['no_of_questions'];
                $query = $this->db->query("SELECT qbank.*,question_category.* FROM qbank JOIN question_category ON qbank.cid = question_category.cid where qbank.cid='$cid' and qbank.did='$did' and qbank.institute_id = '$institute_id' ORDER BY RAND() limit $noq ");
                $qidarr = $query->result_array();

                foreach ($qidarr as $key => $qid) {
                    $qids[] = $qid['qid'];
                }
                $category_names[] = $qid['category_name'];
                $qids_range[] = $rng . "-" . ($key + $rng);
                $rng = $key + 1;
            }
            $time_Spent_ind = array();
            $roids = array();
            for ($x = 1; $x <= count($qids); $x++) {
                $time_Spent_ind[] = "0";
                $roids[] = "0";
            }
            $time_Spent_ind = implode(",", $time_Spent_ind);
            $qids = implode(",", $qids);
            $roids = implode(",", $roids);
            $category_names = implode(",", $category_names);
            $qids_range = implode(",", $qids_range);
            $insert_data = array(
                'uid' => $uid,
                'quid' => $quid,
                'oids' => $roids,
                'qids' => $qids,
                'category_name' => $category_names,
                'qids_range' => $qids_range,
                'start_time' => time(),
                'last_response' => time(),
                'time_spent' => '0',
                'time_spent_ind' => $time_Spent_ind,
                'institute_id' => $institute_id
            );

            if ($this->db->insert('quiz_result', $insert_data)) {
                $rid = $this->db->insert_id();

                // Giảm credit
//                if ($quiz['test_type'] == 1) {
//                    $user['credit'] = $user['credit'] - $quiz['credit'];
//
//                    $this->db->where('id', $uid);
//                    $this->db->update('users', $user);
//                }
                // --
                // không cần nữa vì đã thực hiện làm lại bài test kiểu mới
                // nhưng phải cập nhật doAgainRecord
                $doAgainRecord['Status'] = 'done';
                $this->db->where('Id', $doAgainRecord['Id']);
                $this->db->update('do_again', $doAgainRecord);

                $cookie = array(
                    'name' => 'rid',
                    'value' => $rid,
                    'expire' => '86500'
                );

                $this->input->set_cookie($cookie);
                return "1";
            }
        }
    }

    function quiz_submit($id) {
        $institute_id = $this->session->userdata('institute_id');
        // result id
        $rid = $this->input->cookie('rid', TRUE);

        $noq = $_POST['noq'];
        $oids = array();
        for ($x = 0; $x <= $noq; $x++) {
            if ($_POST['answers' . $x]) {
                $oids[$x] = $_POST['answers' . $x];
            } else {
                $oids[$x] = 0;
            }
        }
//  implode array of selected option ids
        $oid = implode(",", $oids);
// fetch quiz detail
        $query = $this->db->query("select quiz.* from quiz where quid='$id' and institute_id = '$institute_id'");
        $quiz = $query->row_array();
        $correct_score = $quiz['correct_score'];
        $incorrect_score = $quiz['incorrect_score'];
        $min_percentage = $quiz['pass_percentage'];
        // fetch options score
        $query = $this->db->query("SELECT * FROM  q_options WHERE oid IN ( $oid ) and institute_id = '$institute_id'");
        $options = $query->result_array();
        $score = 0;
        // calculate score
        foreach ($options as $value) {
            if ($value['score'] == "1") {
                $score+=$correct_score;
            } else {
                $score+=$incorrect_score;
            }
        }
        // calculate percentage
        $query = $this->db->query("select quiz_result.* from quiz_result where rid='$rid' and institute_id = '$institute_id'");
        $qr = $query->row_array();
        //( obtained score /(number of question * correct score) )*100
        $percentage = ($score / (count(explode(",", $qr['qids'])) * $correct_score )) * 120;
        // user pass or fail
        if ($percentage >= $min_percentage) {
            $q_result = "1";
        } else {
            $q_result = "0";
        }
        $time_spent = time() - $qr['start_time'];
        $insert_data = array(
            'oids' => $oid,
            'end_time' => time(),
            'score' => $score,
            'percentage' => $percentage,
            'q_result' => $q_result,
            'time_spent' => $time_spent,
            'status' => '1'
        );

        $this->db->where('institute_id', $institute_id);
        $this->db->where('rid', $rid);
        if ($this->db->update('quiz_result', $insert_data)) {


            delete_cookie("rid");
            return "Quiz submitted successfully. <a href='" . site_url('result/view/' . $rid . '/' . $id) . "'>Click here</a> to view result";
        } else {

            return "Unable to submit quiz";
        }
    }

}

?>
